import yaml
import urllib
import urllib2
import sqlite3
import csv
import xlrd
import datetime
import calendar
import httplib
import urlparse
import re
import threading
import string
from threading import Thread
from time import gmtime,strftime,time,sleep,ctime,clock
from BeautifulSoup import BeautifulSoup, SoupStrainer
from Queue import Queue

class FinaceRobot:
    config = yaml.load(open('config.yaml'))
    conShare = sqlite3.connect(config['development']['database_share'])
    conn = sqlite3.connect(config['development']['database_finace'])
    
    cursorShare = conShare.cursor()
    cursor = conn.cursor()
    
    table = {
        'quarters':'quarters',
        'urls':'urls'
        }
    
    today = datetime.date.today().strftime("%Y%m%d")
    url_header = config['qq']['header']
    url_footer = config['qq']['footer']
    
    urls = Queue()
    abstract = Queue()
    quarter = Queue()
    crawled_urls = []
    failed_urls = []
    failed_max = 1
    crawled_num  = 0
    counter = 0
    delay = 0.1
    run_start = clock()
    run_finish = clock()
    
    def __init__(self, delay=0.1, retry = 3):
        self.run_start = clock()
        print "************* Update Finace Database At " + str(self.today) + " *************"
        self.delay = delay
        self.failed_max = retry
        self.initDatabase()
        
    def initDatabase(self):
        try:
            self.cursor.execute(
                """
                CREATE TABLE IF NOT EXISTS %s(
                symbol varchar(25),
                url varchar(255),
                visited boolean
                )
                """
                %(self.table['urls'])
                )
            self.cursor.execute(
                """
                CREATE TABLE IF NOT EXISTS %s(
                symbol varchar(25),
                date varchar(10),
                total_profit float,
                sales_profit float,
                net_profit float,
                other_profit float,
                invest_income float,
                income_tax float,
                financing_costs float,
                non_biz_expend float,
                total_assets float,
                current_assets float,
                fixed_assets float,
                intangible_assets float,
                current_liabilities float,
                equity float,
                CFFO float,
                CFFI float,
                CFFF float,
                ATO float,
                ROE float,
                ROA float,
                ROC float                
                )          
                """
                %(self.table['quarters'])
                )
            """
            Commit
            """
            self.conn.commit()            
            print "************* Init Database Finished *************"
            
            self.saveWantedUrls()
            
        except Exception,e:
            print e
            
    def saveWantedUrls(self):
        try:
            while 0 == self.getRecNum('urls'):
                self.cursorShare.execute(
                """
                SELECT symbol from %s
                """
                %('symbols')
                )
                symbols = self.cursorShare.fetchall()
                if None != symbols:
                    for s in symbols:
                        symbol = s[0]
                        url = self.url_header + str(symbol[0:6]) + self.url_footer
                        self.urls.put(url)
                        self.cursor.execute(  
                        """
                        INSERT INTO urls(symbol,url,visited) VALUES (?,?,?)
                        """            
                        ,(symbol,url,'0')
                        ) 
                self.conn.commit()
                return True
        except Exception,e:
            print e
            
            
    def getWantedUrls(self):
        try:
            num = self.getRecNum('urls')
            if 0 != num:
                self.cursor.execute(
                """
                SELECT * from %s WHERE visited = '0'
                """
                %(self.table['urls'])
                )
                urls = self.cursor.fetchall()
                if None != urls:
                    for u in urls:
                        self.urls.put(u)
                    print "************* " + str(self.urls.qsize()) + " urls will be crawled *************"
                    return True
                else:
                    return False
                
            else:
                print "************* No new urls need be crawled *************"
                return False           
            
        except Exception,e:
            print e
            
    def getRecNum(self,table):
        try:
            self.cursor.execute(            
            """
            SELECT count(*) FROM %s
            """
            %(table)
            )
            return self.cursor.fetchone()[0]
        except Exception,e:
            print e
            
    def httpExists(self,url):
        host,path = urlparse.urlsplit(url)[1:3]
        if ':' in host:
            host,port =host.split(':',1)
            try:
                port = int(port)
            except ValueError:
                print 'Invalid port number %r' % (port,)
                return False
        else:
            port = None
            
        try:
            connection = httplib.HTTPConnection(host,port=port)
            connection.request("HEAD",path)
            resp = connection.getresponse()
            if resp.status == 200 or 301 or 400:
                found = True
            elif resp.status == 302 or 404:
                found = self.__httpExists__(urlparse.urljoin(url,resp.getheader('location','')))
            else:
                print "Status %d %s: %s" % (resp.status,resp.reason,url)
                found = False
        except Exception,e:
            print e.__class__,e,url
            found = False
        return found
     
    def getQuarterFromQQ(self):
        args = self.urls.get()
        url = args[1]
        symbol = args[0]
        url_is_exists = self.httpExists(url)
        if url_is_exists:
            request = urllib2.Request(url)
            request.add_header('User-Agent', 'Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-CN; rv:1.8.1.14) Gecko/20080404 (FoxPlus) Firefox/2.0.0.14')
            try:
                page = urllib2.urlopen(request).read()
                sleep(self.delay)
                soup = BeautifulSoup(page)
                tdTag = soup.findAll("td", "" )
                if 86 == len(tdTag):
                    start = 6
                else:
                    start = 7
                num_of_columns = 4
                data = []
                for i in range(start,len(tdTag)):                    
                    index = i - start
                    row = index/num_of_columns
                    content = tdTag[i].contents[0].encode('utf-8').replace(",","").strip()
                    if 0 == row:
                            data.append(content)
                    elif 9 == row or 16 == row:
                        pass
                    else:
                        if '--' == content or '&nbsp;' == content or '' == content or None == content:
                            data.append(float(0))
                        else:
                            data.append(float(str(content[0:-6])))
                self.order(symbol,data)
                self.crawled_urls.append(url)
                print "Getted abstract: " + str(self.abstract.qsize())
                print "Information of " + symbol + " has been crawled."
            except Exception,e:
               print "Information of "  + symbol + " can not been got."
               print e  
        else:
            print "************* The Url Is Not Exist! *************"
            
    def order(self,symbol,data):
        try:
            for i in range(0,4):
                self.getMatrix(symbol,data,i)                
        except Exception,e:
                print e
                
    
    def getMatrix(self,symbol,data,num):
        try:            
            index = 0
            num_of_columns = 4
            matrix = []
            for d in data:                
                row = int(index/num_of_columns)
                column = int(index%num_of_columns)
                if num == column:
                    matrix.append(d)    
                else:
                    pass
                index = index + 1
            matrix.reverse()
            matrix.append(symbol)
            matrix.reverse()
            self.abstract.put(matrix)   
        except Exception,e:
                print e
            
    def getNewUrlsNum(self):
        try:
            self.cursor.execute(  
            """
            SELECT COUNT(*) FROM %s WHERE visited = '0'
            """            
            %(self.table['urls'])
            )
            return self.cursor.fetchone()[0]
        except Exception,e:
               print e
        
        
    def crawl(self,threads = 1000):
        """
        Process finace infomation
        """
        try:
            if self.getNewUrlsNum() < threads:
                threads = self.getNewUrlsNum()
            else:
                pass
            pool = []            
            for index in xrange(0, threads):                
                spider = threading.Thread(target=self.getQuarterFromQQ)
                spider.daemon = True
                pool.append(spider)
                
            for spider in pool:    
                spider.start()
                 
            for spider in pool:
                spider.join()          
            
        except Exception,e:
               print e
        
    def save(self,max = 2000):
        try:
            while True:    
                self.caculate(self.abstract.get())                          
                self.counter +=1                
                if  0 == self.counter%max:                
                    self.conn.commit()
                    print "************* Saving *************"
                    print "************* "+ str(self.counter) +"  records have been saved *************"                    
                else:
                    pass
                if 0 == self.abstract.qsize():
                    self.conn.commit()
                    break
                else:
                    pass
        except Exception,e:
               print e
            
    def caculate(self,data=[]):
        try:
            ATO = 0
            ROE = 0
            ROA = 0
            ROC = 0
            if None != data:
                date = data[1]
                total_profit = data[2]
                sales_profit = data[3]
                net_profit = data[4]
                other_profit = data[5]
                invest_income = data[6]
                income_tax = data[7]
                financing_costs = data[8]
                non_biz_expend = data[9]
                total_assets = data[10]
                current_assets = data[11]
                fixed_assets = data[12]
                intangible_assets = data[13]
                current_liabilities = data[14]
                equity = data[15]
                CFFO = data[16]
                CFFI = data[17]
                CFFF = data[18]
                if 0 != fixed_assets:
                    ATO = round(sales_profit/fixed_assets,8)
                else:
                    pass
                if 0 != equity:
                    ROE = round(net_profit/equity,8)
                else:
                    pass
                if 0 != total_assets:
                    ROA = round(total_profit/total_assets,8)
                else:
                    pass
                if 0 != current_liabilities:
                    ROC = round(current_assets/current_liabilities,8)
                else:
                    pass
                
                result = [ATO,ROE,ROA,ROC]                
                data.extend(result)                
                
                self.cursor.execute(  
                    """
                    INSERT INTO %s VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
                    """            
                    %(self.table['quarters']),
                    data
                    )                
            else:
                pass
        
        except Exception,e:
               print e
               
    def delCrawledUrls(self):
        try:
            for url in self.crawled_urls:
                self.cursor.execute(  
                """
                UPDATE %s SET visited = '1' WHERE url = '%s'
                """            
                %(self.table['urls'],url)
                )
            self.urls = Queue()
            self.conn.commit()
        except Exception,e:
            print e
            
    def run(self):
        try:           
            while 0 != self.getNewUrlsNum():
                self.getWantedUrls()
                self.crawl()
                self.delCrawledUrls()
                self.save()
            if 0 == self.getNewUrlsNum():                
                self.conn.close()
                self.run_finish = clock()
                print "************* Run Time: " + str(self.run_finish - self.run_start) + " *************"
                print "************* All Done! *************"
            else:
                pass
        except Exception,e:
            print e
    